IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('sync_ClientPro_Card_unloss')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE sync_ClientPro_Card_unloss

GO
/*
1.20200508 lrh 新增虚拟卡处理,解挂时更新dt_ac_link
*/
create  procedure [dbo].[sync_ClientPro_Card_unloss] 	--解挂处理过程
(
@user_serial bigint,	--人员序号
@card_serial char(8),	--卡顺序号
@card_hao varchar(20),	--物理卡号
@card_lx int,			--卡类型
@lx int = 0,					--过程类型（停用）
@ip varchar(20),		--ip
@gly_no nvarchar(50),	--管理员
@reg_serial varchar(20)	--企业编号
)
as

set nocount on

declare @xh int		--卡表序号
declare @user_no varchar(100)


		select @card_serial=card_serial,@user_serial=user_serial
		from dt_card where card_hao=@card_hao and card_type=1

		select @user_no = user_no from dt_user where user_serial = @user_serial

	if not exists(select 1 from dt_card where user_serial=@user_serial and card_serial=@card_serial
	and card_hao=@card_hao and card_type=1)

	begin

		select 2	--不存在挂失卡片,无法解挂
			print '不存在挂失卡片,无法解挂,工号:'+@user_no
		return

	end

	begin transaction	--开始事务

		select @xh=max(xh) from dt_card where user_serial=@user_serial and card_serial=@card_serial

		--1.更新卡户状态
		update dt_ac_card
		set ac_state=0
		where user_serial=@user_serial
		and card_serial=@card_serial

		--2.更新卡表状态
		update dt_card
		set card_type=0,sj=getdate()
		where user_serial=@user_serial
		and card_serial=@card_serial

		--3.更新人员档案卡号
		update dt_user
		set user_card=@card_hao
		where user_serial=@user_serial

		--4.更新dt_ac_link表
		update dt_ac_link
		set card_hao=@card_hao,card_serial=@card_serial
		where user_serial=@user_serial

		--4.生成卡号转换格式表数据
		exec ClientPro_Card_formate @xh,@card_lx

		--5.生成增量更新日志20151021
			insert into wt_public(lx,log_type,is_all,user_serial,card_serial,log_sj,log_ip,gly_no,new_number,old_number)
			values(8,7,0,@user_serial,@card_serial,getdate(),@ip,@gly_no,0,0)
		--insert into WT_USER_UP(lx,log_type,card_serial,user_serial,log_sj,log_ip,gly_no)
		--values (2,1,@card_Serial,@user_serial,getdate(),@ip,@gly_no)

		--6.生成卡片解挂日志
		insert into WT_CARD_LOG(
		lx,log_type,log_state,user_serial,card_old,card_new,
		log_xh,log_sj,log_ip,gly_no,log_erro,log_lx,regserial
		)values(
		2,0,0,@user_serial,@card_hao,@card_serial,
		@card_lx,getdate(),@ip,@gly_no,0,0,@reg_serial
		)


		--7.返回成功
		select 1

	commit transaction




GO

